【实操篇】新年计划表，就该这样做
作者 l 彩色石头
来自优秀学员投稿
前天拉登老师发布了我的文章《你的新年计划落地，还差这一个表格》。(点击链接查看)
很平常的一件事情，不知道拉登老师为什么这么兴奋。
有什么好兴奋的吗？真的高兴不起来。公司里的事情，天天加班到八九点才下班，回来还要被拉登老师催稿。
终于全部整理完了，好想骂自己，为什么这么找事？
累了一天了，好好的刷个电视剧不行吗？
累了一天了，躺床上刷刷抖音不行吗？
累了一天了，早点睡个觉不行吗？
本来只是有些心痒，学到的东西不用一下，感觉不爽。
可真到自己做起来，也只能是走一步看一步了。
那个用切片器控制的完成率图，事实上开了一天的天窗，真的把现在学的和以前积累的都掏空了，几乎想要放弃了。
还好
坚持下来了
终于将各种知识串接起来、能够综合应用了
这种感觉
太幸福了
接下来，我想要把这份幸福传递给你。分享一下这个表格的制作方法。
具体的过程，可以拆分成下面几个子幸福。
1）甘特图的制作
2）日程表的制作
3）完课率图表制作
4）图表整合
没有幸福是从天而降的，这几个幸福也充满了坎坷，如果你在学习幸福过程中感到困难，请记住我的一句话：
01甘特图部分
首先是甘特图的制作。
「总计划_季度_NEW」中的甘特图↑
甘特图可以通过颜色、标记，在时间轴上，直观的呈现出项目的进度。制作方法主要包含3个部分：
1）日历的制作
2）下拉菜单的制作
3）单元格标记的制作
1）日历的制作
【分析】
可以看到日历包含了两行的内容，第1行是月份，第2行是具体的几号。
和传统的日期表头相比，把月份独立出来，只在月初显示月份，很明显清晰了很多。
【要点】
接下来是计算周日日期公式：
=$O$1+(7-WEEKDAY($O$1,2))+(COLUMN(A1)-1)*7
公式有些长有些复杂，不用怕，拆解成单个的部分，更容易理解一些。
1.1）判断2019年1月1日是星期几，WEEKDAY($O$1,2)
1.2）计算距离周日的天数，(7-WEEKDAY($O$1,2))
1.3）得出周日的日期，$O$1+(7-WEEKDAY($O$1,2))
1.4）计算后续周日的日期，$O$1+(7-WEEKDAY($O$1,2))+(COLUMN(A1)-1)*7
【显示】
完成了上面公式的编写，得到的结果是这样的。
和预期的还是不太一样，我们还需要完成下面的操作。
1.5）自定义单元格格式：将日期显示为“m”、“d”，实际内容还是日期，便于后继的操作
1.6）条件格式：只在第一星期显示月份，条件，DAY(C2)>=7，格式，文字颜色同单元格背景色，即不显示文字
2）下拉菜单的制作
这个大家应该都非常熟悉了，制作下拉菜单必须使用「数据验证」功能。
在【数据】选项卡中，点击【数据验证】，选择【序列】，设置序列内容为「1,2,3」，注意逗号必须是英文的。
3）单元格标记的制作
甘特图中的标记形式，大致可以分为两类：标记和颜色块。
好在这两种形式，都是使用同一个功能实现的：条件格式。
标记的制作
①给单元格添加条件格式，选择第1个「基于各自值。。。」的选项，设置「格式样式」为图标集，勾选「仅显示图标」
②然后分别为显示三种符号
色块的制作
类似的方法，条件格式选择最后1项「使用公式。。。」，把规则公式填入到下面的条件中。
给符合条件的单元格，设置填充颜色。
然后再使用相同的方法，创建其他颜色的条件格式。
这样甘特图标记和颜色填充的部分就讲完了。
02日程表
接下来，第2个部分是「日计划」中的日程表制作讲解。
同样，我们拆分成2点来讲，不会那么容易犯困。
1）整理数据
2）制作数据透视表
1）整理数据
首先日程表里的打卡标记的做法，和前面甘特图中的标记制作方法是一致的，我就不再赘述了。
【分析】
接下来的问题就是，如何快速的对打卡的结果进行统计呢？
从「问题-目标-方法」的这个框架来思考，可以这样来实现。
「问题」：二维表格，用于填写每日进度，最左列是任务列，第二行是日期行，交叉部分是任务的每日执行情况
「目标」：清单表格，方便数据透视表处理的数据源
「方法」：二维转一维，用Power Query工具
看完文字的描述，你可能处于懵的状态，这是正常反应，看过下面的动态演示，你就非常清楚。
首先，是完成数据的清洗和规范化
1- 新建查询，从工作簿
2- 删除多余的行和列
3- 提升日期行为标题
然后，是把二维表转换成一维表。
1- 逆透视列
2- 设置日期类型
3- 关闭并上载，仅创建连接
这几个过程看上去很复杂，其实真的很复杂，完成后Power Query中显示的操作步骤如下：
【要点】
第1，从Excel工作簿建立查询，是为了提取“日计划”表的第二行日期数据；更新日计划表的内容，一定保存了文件之后，查询的结果才会刷新
第2，Power Query中日期列的数据，要经过两次数据类型的转换，先转为整数，后才能转为日期
第3，上载查询时，选择仅创建连接的方式，可以减少数据刷新的操作，只刷新一个数据透视表，就完成所有数据的更新
2）制作透视表
把二维表转成一维表之后，数据统计起来就方便多了，因为我们可以使用效率神器：数据透视表。
1- 制作数据透视表
2- 选择使用外部数据，选新建的查询
3- 选择添加到数据模型
创建好数据透视表之后，进行数据字段布局，布局规则：
行：选择日期
列：选择任务
值：为求和方式
数据透视表的强大之处就在于，它可以直接创建图表，而且还可以使用「切片器」「日程表」这样的神器。
1- 制作数据透视图，选择柱形图
2- 插入日程表，设置日期间隔为日
【要点】
选择添加到数据模型，相同数据源的多个数据透视表共用一个模型，只需一次刷新，所有数据透视表的数据都同步更新。
我知道你可能撑不住了，但是请记住我开头说的话：
03完成率图
最后这个完成率的图就厉害了，它由两个部分组成：
一是，显示完成率的圆环图
二是，选择不同任务的切片器
三是，图表整合
1）圆环图
再好看的图表都离不开数据，所以第1步要做的就是：数据准备。
【数据准备】
先使用数据透视表，统计“日计划”表中各项任务完成的次数。
然后制作图表需要的数据
1、指标列：根据“总计划”表，得出每项任务的总执行次数
2、完成列：从数据透视表中，查询出任务完成的次数
=IFNA(VLOOKUP(D2,$A$2:$B$7,2,0),0)
3、未完成列： 指标列-完成列
4、完成率列：完成列/指标列
【制作图表】
用完成、未完成两列数制作圆环图
插入文本框，内容链接到相应的完成率
具体操作参见网易云课堂《Office三合一》第二章第一节
点击「阅读原文」了解三合一课程
【要点】
1、每一个任务制作一张圆环图
2、调整单元格的行高和列宽，使每个圆环图占用一个单元格位置
2）切片器
切片器是透视表中非常神奇的一个功能，可以轻松的制作出动态交互的图表。
【分析】
根据图表中的各个组成部分，我们分析对应的实现方法：
1、切片器：可用数据透视表生成
2、动态图表：可用粘贴为链接的图片（照相机）+定义名称实现
3、切片器与动态图表的关联：定义名称中引用数据透视表的值
【制作切片器】
1、制作辅助表，序号与圆环图的顺序一致
2、制作数据透表
3、插入切片器：任务列
4、应用切片器，生成序号的单元格，在自定义名称公式中，写入该单元格名称
【定义名称】
接下来要实现，点击课程名称，显示对应完成率的圆环图，这里要使用【定义名称】功能。
1、定义名称：pic
2、公式：=OFFSET(完成率表!$H$13,0,完成率表!$B$27,1,1)
从第一个圆环图左边单元格开始，向右偏移由数据透视表序号列返回的位数。
【制作动态图表】
最后一步，就是把图片动态的引用过来了，秘籍就是使用「链接的图片」。
1、选中圆环所在的单元格，复制（建议用方向键选择）
2、选择性粘贴，链接的图片
3、更改链接为定义的名称
3）图表整合
最后，将所有的图表，放在总计划表中，调试一下，完成了！
04心得
写下来是最好的复盘！
表格做出来的时候，成就感爆棚，马上发到朋友圈、微信群晒，获得大家的点赞和夸奖。让所有人都知道我的Excel成长了。
我一直都觉得自己做的很棒，其实这是一个误区。
每个人看自己的优点，就跟去挑别人的缺点一样，非常的简单，但也是最没有技术含量的。
真正厉害的是，发现别人的不足，还能提出建议，帮助别人解决。
真正厉害的是，了解自己的长处，还能总结出经验，把别人教会。
这两篇文章写下来，真难！
自己想明白是一回事，要让别人了解清楚是另一回事，考验的是表达的技巧和能力。
怎样才能把内部逻辑讲清楚？
怎样才能让别人能跟着步骤，就可以做出来？
开始只是步骤的罗列，然后写出详细的操作步骤，又给操作步骤准备了图示版、甚至GIF版。
表格是查了一遍，又一遍，生怕有漏洞。
这样磨下来，表格我是肯定不会忘了，我还学会了更重要的表达、写作的能力。
开始看到这个训练营的广告，我是反感的，学个Excel还要交那么多的钱，自己看视频不就好了吗？
现在再来看训练营，一遍遍地做作业，写作业步骤、写学习心得，学员作业互评，帮其他同学解决问题，这些也给我今天的文章，积攒了不少的经验。
05表格下载
好了，这里应该是文末了，前面说好的文末有表格下载，公众号后台回复「计划表」，现在完整的案例。
其实Excel提高效率，最终还是取决于思路，否则只会有越来越多的公式。
如果彩色石头的文章对你的思路有所启发，就打赏支持一下她吧。
我是拉小登，源于Excel，不只是Excel